package com.engine.workflow.cmd.requestForm;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.cloudstore.dev.api.util.TextUtil;
import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.biz.EncryptConfigBiz;
import com.engine.common.entity.BizLogContext;
import com.engine.common.util.ParamUtil;
import com.engine.core.interceptor.CommandContext;
import com.engine.workflow.biz.requestForm.LinkageLimitBiz;
import com.engine.workflow.biz.requestForm.LinkageLogBiz;
import com.engine.workflow.biz.requestForm.RequestFormBiz;
import com.engine.workflow.entity.requestForm.FieldInfo;
import com.engine.workflow.entity.requestForm.FieldValueBean;
import com.api.workflow.util.ServiceUtil;

import weaver.conn.ConnStatement;
import weaver.conn.RecordSet;
import weaver.general.Util;
import weaver.hrm.HrmUserVarify;
import weaver.hrm.User;
import weaver.hrm.resource.ResourceComInfo;
import weaver.workflow.automatic.automaticconnect;
import weaver.workflow.workflow.WorkflowConfigComInfo;

/**
 * 字段SQL赋值联动请求
 * @author liuzy 2017/5/4
 */
public class LinkageFieldSqlCmd extends AbstractCommonCommand<Map<String,Object>>{

	private HttpServletRequest request;
	private Map<String,String> specialVarMap = new HashMap<String,String>();
	private Map<String,Object> result = new HashMap<String,Object>();
	private Map<String,FieldInfo> fieldinfo = new HashMap<String,FieldInfo>();
	private Map<String,FieldInfo> tbfieldinfo = new HashMap<String,FieldInfo>();
	private Map<String,Object> linkageSqlMap= new HashMap<>();
	public LinkageFieldSqlCmd(HttpServletRequest request, User user){
		this.request = request;
		this.user = user;
	}

	/**
	 * 请求SQL联动结果
	 */
	public Map<String, Object> execute(CommandContext commandContext){
		this.initSpecialVariable();
		int formid = Util.getIntValue(request.getParameter("formid"), 0);
		int isbill = Util.getIntValue(request.getParameter("isbill"));
		this.fieldinfo = RequestFormBiz.getFieldInfoByFormid(formid+"", isbill+"");
		this.tbfieldinfo = RequestFormBiz.tranFieldInfo(this.fieldinfo);
		ConnStatement statement = null;
		try {
			//记录联动日志
			Map<String, Object> reqParams = ParamUtil.request2Map(request);
			String uuid=  UUID.randomUUID().toString().replace("-", "").toUpperCase();
			LinkageLogBiz linkageLogBiz = new LinkageLogBiz(user,1,reqParams,uuid);
			boolean needWrite = linkageLogBiz.needWriteLog();
			if(needWrite){
				linkageLogBiz.writeLinkageLog();
			}
			statement = new ConnStatement();	//本地数据源共用，并统一关闭
			String linkageid = Util.null2String(request.getParameter("linkageid"));
			for(String keyid : linkageid.split(",")){
				this.executeSingleSqlLinkage(Util.getIntValue(keyid), statement);
			}
			if(needWrite){
				//联动结果回写
				linkageLogBiz.setLinkageResult(this.result);
				linkageLogBiz.setLinkageSqlMap(this.linkageSqlMap);
				new Thread(linkageLogBiz).start();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (statement != null) {
				statement.close();
				statement = null;
			}
		}
		return this.result;
	}
	
	@Override
	public BizLogContext getLogContext() {
		return null;
	}
	
	/**
	 * 单条联动设置触发
	 */
	private void executeSingleSqlLinkage(int keyid, ConnStatement statement){
		Connection conn = null;
		Statement stmt = null;
		ResultSet resultset = null;
		automaticconnect automaticconnect = new automaticconnect();
		RecordSet rs = new RecordSet();
		String linkageSql = "";
		try { 		//单条联动配置try-catch，避免影响整体
			int assignField = 0;
			String sqlcontent = "", datasourceid = "";
			rs.executeSql("select * from workflow_nodefieldattr where id=" + keyid);
			if(rs.next()){
				assignField = Util.getIntValue(rs.getString("fieldid"), 0);
				sqlcontent = Util.null2String(rs.getString("attrcontent"));
				datasourceid = Util.null2String(rs.getString("datasourceid"));
			}
			String rowIndexStr = Util.null2String(request.getParameter("rowIndexStr_"+keyid));
			if("".equals(sqlcontent) || "".equals(rowIndexStr) || assignField == -9 || !fieldinfo.containsKey(assignField+""))
				return;
			
			int index = sqlcontent.indexOf("doFieldSQL(\"");
			if (index > -1) {
				sqlcontent = sqlcontent.substring(index + 12);
				index = sqlcontent.lastIndexOf("\")");
				if (index > -1)
					sqlcontent = sqlcontent.substring(0, index);
				sqlcontent = sqlcontent.trim();
			} else
				return;

			//用于存储超出限制的sql信息
			Map<String,Object> outOfLimitMap = new HashMap<String,Object>();
			//用于存储执行的sql语句，和sql语句执行结果
			Map<String,String> sqlResultMap = new HashMap<String,String>();

			//循环行，主字段联动为一行(-1)，明细联动可一行或多行(0,1,2)
			Map<String,FieldValueBean> changeValue = new HashMap<>();
			for(String str : rowIndexStr.split(",")){
				int rowIndex = Util.getIntValue(str, -2);
				if(rowIndex < -1)	continue;
				String execSql = parseExecSql(sqlcontent, rowIndex);
				if("".equals(execSql))
					continue;
				//是否超出限制
				boolean resultOutOfLimit = false;
				//sql查询结果总条数
				int count = 0 ;
				// 执行SQL，查询结果
				List<String> firstCol = new ArrayList<String>();
				List<String> secondCol = new ArrayList<String>();
				int selfieldcount = 1;
				try {
					String sqltmp = execSql.substring(0, execSql.toUpperCase().indexOf("FROM"));
					if (sqltmp.indexOf(",") > -1)
						selfieldcount = 2;
				} catch (Exception e) {
				}
				linkageSql = execSql;
				String fieldkey = rowIndex>=0 ? assignField+"_"+rowIndex : assignField+"";
				if ("".equals(datasourceid)) {	// 本地数据源
					try {
						count = LinkageLogBiz.getCount(execSql,statement,null,false);
						sqlResultMap.put("field"+fieldkey,execSql+"========count:"+count);
						resultOutOfLimit = LinkageLimitBiz.checkFieldSqlResultIsOutOfLimit(execSql,statement,null,false);
						//没超出限制，才执行
						if (!resultOutOfLimit){
							statement.setStatementSql(execSql);
							statement.executeQuery();
							while (statement.next()) {
								String col_tmp = Util.null2String(statement.getString(1));
								firstCol.add(col_tmp);
								if (selfieldcount > 1) {
									try{
										col_tmp = Util.null2String(statement.getString(2));
										secondCol.add(col_tmp);
									}catch (Exception e){}
								}
							}
						}
					} catch (Exception e) {
					}
				} else {	// 外部数据源
					try {
						conn = automaticconnect.getConnection("datasource." + datasourceid);// 获得外部连接
						stmt = conn.createStatement();
						count = LinkageLogBiz.getCount(execSql,null,stmt,true);
						sqlResultMap.put("field"+fieldkey,execSql+"========count:"+count);
						resultOutOfLimit = LinkageLimitBiz.checkFieldSqlResultIsOutOfLimit(execSql,null,stmt,true);
						//没超出限制，才执行
						if(!resultOutOfLimit){
							resultset = stmt.executeQuery(execSql);
							while (resultset.next()) {
								String col_tmp = Util.null2String(resultset.getString(1));
								firstCol.add(col_tmp);
								if (selfieldcount > 1) {
									try {
										col_tmp = Util.null2String(resultset.getString(2));
										secondCol.add(col_tmp);
									}catch (Exception e){}
								}
							}
						}
					} catch (Exception e) {
					} finally {
						if (resultset != null)
							resultset.close();
						if (stmt != null)
							stmt.close();
						if (conn != null)
							conn.close();
					}
				}
				FieldInfo assignFieldInfo = fieldinfo.get(assignField+"");
				changeValue.put("field"+fieldkey, this.buildValueObj(firstCol, secondCol, assignFieldInfo, "field"+fieldkey));
				if (resultOutOfLimit){
					outOfLimitMap.put("field"+fieldkey,execSql);
				}
			}
			Map<String,Object> assignInfo = new HashMap<String,Object>();
			assignInfo.put("changeValue",changeValue);
			boolean showAI = "1".equals(Util.null2String(request.getParameter("showAI")));
			if(showAI)
				assignInfo.put("linkageSql", TextUtil.toBase64(linkageSql));
			boolean resultOutOfLimit = false;
			if (!outOfLimitMap.isEmpty()) resultOutOfLimit = true;
			assignInfo.put("resultOutOfLimit", resultOutOfLimit);
			if (resultOutOfLimit)   //如果超出限制，执行的sql集合传到前台
				assignInfo.put("outOfLimitMap", outOfLimitMap);
			result.put("assignInfo_"+keyid, assignInfo);
			linkageSqlMap.put("linkagesql_"+keyid,sqlResultMap);
		} catch (Exception e) {
			// e.printStackTrace();
		} finally {
			try {
				if (resultset != null)
					resultset.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * 生成特殊变量值信息
	 */
	private void initSpecialVariable(){
		try {
			RecordSet rs = new RecordSet();
			ResourceComInfo resourceComInfo = new ResourceComInfo();
			int creater = user.getUID();
			int credept = Util.getIntValue(resourceComInfo.getDepartmentID("" + creater), 0);
			int currentuser = user.getUID();
			int currentdept = Util.getIntValue(resourceComInfo.getDepartmentID("" + currentuser), 0);
			int requestid = Util.getIntValue(Util.null2String(request.getParameter("requestid")));
			if (requestid > 0) {
				rs.executeSql("select creater from workflow_requestbase where requestid=" + requestid);
				if (rs.next()) {
					creater = Util.getIntValue(rs.getString(1));
					credept = Util.getIntValue(resourceComInfo.getDepartmentID("" + creater), 0);
				}
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			specialVarMap.put("requestid", (requestid>0 ? requestid : 0)+"");
			specialVarMap.put("currentuser", "" + currentuser);
			specialVarMap.put("currentdept", "" + currentdept);
			specialVarMap.put("wfcreater", "" + creater);
			specialVarMap.put("wfcredept", "" + credept);
			specialVarMap.put("currentdate", "" + sdf.format(new Date()));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 解析SQL语句
	 */
	private String parseExecSql(String sqlcontent, int rowIndex) {
		try {
			//替换特殊变量
			for(Map.Entry<String, String> entry : specialVarMap.entrySet()){
				sqlcontent = sqlcontent.replaceAll("\\$" + entry.getKey() + "\\$", entry.getValue()); 
			}
			//替换字段
			ArrayList<String> haveReplace = new ArrayList<String>();
//			Matcher m = Pattern.compile("(\\$)(\\d+)(\\$)").matcher(sqlcontent);
//			while(m.find()){
//				String fieldid = m.group(2);
//				if(haveReplace.indexOf(fieldid) == -1 && fieldinfo.containsKey(fieldid)){	//避免重复替换
//					boolean isdetail = fieldinfo.get(fieldid).getIsdetail() == 1;	//明细联动取主表字段情况
//					String fieldMark = "field"+fieldid+(isdetail ? ("_"+rowIndex) : "");
//					String fieldvalue = Util.null2String(request.getParameter(fieldMark));
//					sqlcontent = sqlcontent.replaceAll("\\$" + fieldid + "\\$", fieldvalue);
//				}
//				haveReplace.add(fieldid);
//			}
			//替换字段 --兼容老的的数据
			Matcher m = Pattern.compile("(\\$)(\\d+|(detail|main)[^\\$]+)(\\$)").matcher(sqlcontent);
			while(m.find()){
				String tb_fieldname = m.group(2);
				String fieldid = "";
				if (RequestFormBiz.isFieldid(tb_fieldname)&&fieldinfo.containsKey(tb_fieldname)){   //老的数据  $fieldid$
					fieldid = tb_fieldname;
				}else if (tbfieldinfo.containsKey(tb_fieldname)){   //新数据  $tablemark.fieldname$
					fieldid = tbfieldinfo.get(tb_fieldname).getFieldid()+"";
				}

				if(haveReplace.indexOf(tb_fieldname) == -1 && !"".equals(fieldid)){	//避免重复替换
					boolean isdetail = fieldinfo.get(fieldid).getIsdetail() == 1;	//明细联动取主表字段情况
					String fieldMark = "field"+fieldid+(isdetail ? ("_"+rowIndex) : "");
					String fieldvalue = Util.null2String(request.getParameter(fieldMark));
					fieldvalue = EncryptConfigBiz.getDecryptData(fieldvalue);
					sqlcontent = sqlcontent.replace("$" + tb_fieldname + "$", fieldvalue);
				}
				haveReplace.add(tb_fieldname);
			}

			//禁止操作数据库脚本语句
			String sqlUpper = sqlcontent.toUpperCase();
			String[] regs = {"\\bUPDATE\\b","\\bINSERT\\b","\\bDELETE\\b","\\bDROP\\b","\\bALTER\\b","\\bCREATE\\b","\\bTRUNCATE\\b","\\bEXEC\\b","\\bCALL\\b"};
			for (String reg : regs) {
				Pattern pattern = Pattern.compile(reg);
				if (pattern.matcher(sqlUpper).find())
					throw new Exception("SQL "+sqlcontent+" exist great risk");
			}
			return sqlcontent;
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
	}
	
	private FieldValueBean buildValueObj(List<String> firstCol, List<String> secondCol, FieldInfo fieldInfo, String fieldMark){
		int requestid = Util.getIntValue(Util.null2String(request.getParameter("requestid")));
		FieldValueBean bean = new FieldValueBean();
		int htmltype = fieldInfo.getHtmltype();
		int detailtype = fieldInfo.getDetailtype();
		StringBuilder value = new StringBuilder();
		if(htmltype == 1 && (detailtype == 2 || detailtype == 3 || detailtype == 4 || detailtype == 5)){	//数值字段类型只取结果集合第一条
			value.append(firstCol.size() == 0 ? "" : firstCol.get(0));
			if(value.toString().startsWith(".") && detailtype == 3)
				value.insert(0,"0"); //QC:#1085344
			if ("".equals(value.toString().trim()))
				value = new StringBuilder().append("0");
		}else{
			if(htmltype == 3){		//给浏览框赋值
				boolean existTwoCol = secondCol.size() > 0;		//是否存在两列，id存在于第二列
				for(String str : existTwoCol ? secondCol : firstCol){
					if("".equals(str)) continue;
					value.append(str).append(",");		//以逗号分隔
				}
			}else{
				for(String str : firstCol){
					value.append(str).append(" ");	//以空格分离
				}
			}
			if(!"".equals(value.toString()))
				value = new StringBuilder().append(value.substring(0, value.length()-1));
		}
		Map<String,Object> otherParams = new HashMap<>();
		String cusDependFieldValues = Util.null2String(request.getParameter("customDepend_"+fieldMark));
		if(!"".equals(cusDependFieldValues))
			otherParams.put("cusDependFieldValues", cusDependFieldValues);
		bean = RequestFormBiz.generateFieldValueObj(value.toString(), fieldInfo, user.getLanguage(),requestid,otherParams);
		return bean;
	}

}
